Groovy基本語法(4)-Groovy JDBC語法(1)以MySQL為例

DAY 9

邊看邊學Groovy/Grails/Gradle系列第 6 篇

groovy jdbc

joombuopre

2013-10-01 23:27:41

4547 瀏覽

分享至

Groovy存取Database也是透過JDBC機制，只是Groovy的語法比較簡單加上Groovy支援closure，像是select * from xxx，然後print出來，語法比起純java的JDBC少了很多行，今天時間關係只能示範最簡單的方法，未來幾天陸續介紹相關方法，另外平常日真的忙碌，每天只能分享些皮毛，假日再與大家分享Groovy如何輕鬆處理XML
要存取Database之前必須兩件事情要做好:

import Groovy.sql.Sql
加入JDBC driver的jar到classpath中(我是以mysql 5.5為例)

簡單示意圖(我想大家都知道吧!?)擷取自Groovy In Action

再來廢話一下，連接資料庫不外乎:

資料庫的URI
username
password

以下就以簡單範例說明Groovy連結MySQL並以簡單SQL語法印出資料

import groovy.sql.Sql //使用Groovy JDBC必須import &注意大小寫

//Sql.newInstance意即建立一個連線
def conn=Sql.newInstance("jdbc:mysql://localhost/goo", // DB的URI
                                            "root",    // UserName
                                            "lga2011", // Password
                                            "com.mysql.jdbc.Driver") //Driver, 要記得加入jar檔
                                            
def records="select count(*) from sysdatepriority where priority = 1"

conn.eachRow(records){ count-> println "Total Records are ${count[0]}"} //當然支援SQL函數囉                                        

conn.eachRow("select * from sysdatepriority where priority =1"){ //eachRow方法類似於while(resultset.next())
    println "System: ${it.system}, Deadline: ${it.deadline}, Priority: ${it.priority}" //用GString印出每一筆資料
}

輸出結果:

Total Records are 31
System: C85, Deadline: 2013/05/30, Priority: 1
System: H23, Deadline: 2013/07/31, Priority: 1
System: K15, Deadline: 2013/05/30, Priority: 1
System: N11, Deadline: 2013/06/15, Priority: 1
System: N15, Deadline: 2013/05/15, Priority: 1
System: N23, Deadline: 2013/05/30, Priority: 1
System: N31, Deadline: 2013/07/30, Priority: 1
System: N32, Deadline: 2013/05/30, Priority: 1
System: N37, Deadline: 2013/06/15, Priority: 1
System: N38, Deadline: 2013/05/30, Priority: 1
System: N43, Deadline: 2013/04/30, Priority: 1
System: N61, Deadline: 2013/04/30, Priority: 1
System: P11, Deadline: 2013/05/15, Priority: 1
System: P27, Deadline: 2013/04/30, Priority: 1
System: P28, Deadline: 2013/05/15, Priority: 1
System: P30, Deadline: 2013/06/15, Priority: 1
System: P51, Deadline: 2013/05/30, Priority: 1
System: P54, Deadline: 2013/06/30, Priority: 1
System: P56, Deadline: 2013/06/30, Priority: 1
System: P61, Deadline: 2013/05/15, Priority: 1
System: R10, Deadline: 2013/05/15, Priority: 1
System: R14, Deadline: 2013/06/30, Priority: 1
System: T40, Deadline: 2013/04/15, Priority: 1
System: T55, Deadline: 2013/03/31, Priority: 1
System: T57, Deadline: 2013/06/30, Priority: 1
System: T64, Deadline: 2013/05/30, Priority: 1
System: W12, Deadline: 2013/06/30, Priority: 1
System: Y11, Deadline: 2013/06/30, Priority: 1
System: Y47, Deadline: 2013/06/15, Priority: 1
System: Y53, Deadline: 2013/05/15, Priority: 1
System: Y54, Deadline: 2013/04/30, Priority: 1

除此之外，Groovy版的JDBC當然也可以用語法create table schema，以及以下的方法

void  eachRow  String statement { row -> code }
void  eachRow  String prepStmt, List values { row -> code }
void  eachRow  GString prepStmt { row -> code }
void  query  String statement { resultSet -> code }
void  query  String prepStmt, List values { resultSet -> code }
void  query  GString prepStmt { resultSet -> code }
List  rows  String statement
List  rows  String prepStmt, List values
List  rows  GString prepStmt
Object  firstRow  String statement
Object  firstRow  String prepStmt, List values
Object  firstRow  GString prepStmt

看到prepStmt對於java人應該不陌生吧!?往後幾天再跟大家分享相關方法如何用